/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

import Model.Zona;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import net.sf.jasperreports.engine.JRExporter;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.util.JRLoader;

/**
 *
 * @author Rossy
 */
public class BaseDatos {
    
    Connection connection = null;
    ResultSet result = null;
    private final String url = "jdbc:microsoft:sqlserver://";
    private final String serverName = "192.168.1.51";
    private final String portNumber = "1433";
    private final String databaseName = "SecurityDB";
    private final String userName = "casdeveloper";
    private final String password = "casdeveloper";
    private final String selectMethod = "Direct";

    public ArrayList<Zona> busqueda(int hhI,int minI,int segI,int hhF,int minF,int segF,int idZona) {
        ArrayList<Zona> zonaList = new ArrayList<Zona>();
        Zona zona = null;
        try {
            connection = this.getConnection();
            if (connection != null) {
                Statement select = connection.createStatement();
                PreparedStatement pstmt = null;
                String query = null;
                //Realiza la busqueda por id
                if ((hhI != 0) &&(hhF!=0) ) {
                    query = "SELECT * FROM ZONA where zona=" + idZona;
                    pstmt = connection.prepareStatement(query);
                    result = pstmt.executeQuery();
                    while (result.next()) {
                        zona = new Zona();
                        zona.setNumZona(result.getInt("id"));
                        zona.setVelocidadPromedio(result.getDouble("vel_prom"));
                        zona.setCantVehiculos(result.getInt("cant_veh"));
                        zona.setCantSemaforos(result.getInt("cant_sem"));
                        zonaList.add(zona);
                    }
                    if (zona==null){
                       JOptionPane.showMessageDialog(null,"Los datos ingresados no son correctos o no existen", 
                                                     "Mensaje de Error",JOptionPane.ERROR_MESSAGE);   
                    }
                }
                //muestra todos los datos de la tabla
                else {
                    result = select.executeQuery("select * from Zona;");
                    while (result.next()) {
                        zona = new Zona();
                        zona.setNumZona(Integer.parseInt(result.getString(1)));
                        zona.setVelocidadPromedio(Double.parseDouble(result.getString(2)));
                        zona.setCantVehiculos(Integer.parseInt(result.getString(3)));
                        zona.setCantSemaforos(Integer.parseInt(result.getString(4)));
                        zonaList.add(zona);
                    }
                }
                result.close();
                result = null;
                closeConnection();
            } //fin del if(connection!=null)

        } catch (Exception e) {
            e.printStackTrace();
        }
        return zonaList;
    }

    public ArrayList<Zona> queryByID(int id){
        ArrayList<Zona> zonaList = new ArrayList<Zona>();
        Zona zona = null;
        try {
            connection = this.getConnection();
            if (connection != null) {
                PreparedStatement pstmt = null;
                String query = null;

                if (id != 0) {
                    query = "SELECT * FROM Zona where id=" + id;
                    pstmt = connection.prepareStatement(query);
                    result = pstmt.executeQuery();
                    while (result.next()) {
                        zona = new Zona();
                        zona.setNumZona(result.getInt("id"));
                        zona.setCantSemaforos(result.getInt("cant_sem"));
                        zona.setVelocidadPromedio(result.getDouble("vel_prom"));
                        zona.setCantVehiculos(result.getInt("cant_veh"));
                        zonaList.add(zona);
                    }
                }
                
                result.close();
                result = null;
                closeConnection();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return zonaList;
    }
    
    public int buscarID(){
        int id=0;
        int fila=0;
        
        try {
            connection = this.getConnection();
            if (connection != null) {
                PreparedStatement pstmt = null;
                String query = null;
                
                query = "SELECT * FROM Zona";
                pstmt = connection.prepareStatement(query);
                result = pstmt.executeQuery();
                while (result.next()) {
                      fila++;    
                }
                
                result.close();
                result = null;
                closeConnection();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
         id=fila+1;
        return id;
    }
    
     public boolean generarReporte(List lista){
        boolean resp=false;
               
        try {
            connection = this.getConnection();
            if (connection != null) {
                JasperReport reporte = (JasperReport) JRLoader.loadObjectFromFile("report1.jasper");
                JasperPrint jprint = JasperFillManager.fillReport(reporte, null, connection);
                
                //Reporte en PDF
                JRExporter exporter = new JRPdfExporter();
                exporter.setParameter(JRExporterParameter.JASPER_PRINT, jprint);
                exporter.setParameter(JRExporterParameter.OUTPUT_FILE, new File("ReporteZona.pdf"));
                exporter.exportReport();
            }
            closeConnection();
            resp=true;     
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return resp;
    }
     
    public boolean login(String usuario,String password){
        boolean resp=false;
        Zona login;
        
        try {
            connection = this.getConnection();
            if (connection != null) {
                PreparedStatement pstmt = null;
                String query = null;
                
                query = "SELECT * FROM USUARIO where usuario='" +usuario+ "' and password='"+password+"'" ;
                pstmt = connection.prepareStatement(query);
                result = pstmt.executeQuery();
                while (result.next()) {
                    resp=true;
                }
                result.close();
                result = null;
                closeConnection();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return resp;
    } 

    private String getConnectionUrl() {
        return url + serverName + ":" + portNumber + ";databaseName="
                + databaseName + ";selectMethod=" + selectMethod + ";";
    }

    private java.sql.Connection getConnection() {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            System.out.println("Connection: " + getConnectionUrl());
            connection = java.sql.DriverManager.getConnection(getConnectionUrl(),
                    userName, password);
            if (connection != null) {
                System.out.println("Connection Successful!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    private void closeConnection() {
        try {
            if (connection != null) {
                System.out.println("\nClosing connection...");
                connection.close();
            }
            connection = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
}

